﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class UserManagerDataAccess
    {
        const string ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";

        public static bool InsertUser(User user)
        {
            string sqlSentence = "INSERT INTO Person VALUES(@Id,@Telephone,@Password,@Name,@Surname1,@Surname2,@Address,@CellPhone,@Occupation)";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (user == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Id", user.Id);
                if(!string.IsNullOrEmpty(user.Telephone))
                    cmd.Parameters.AddWithValue("@Telephone", user.Telephone);
                else
                    cmd.Parameters.AddWithValue("@Telephone", DBNull.Value);

                cmd.Parameters.AddWithValue("@Password", user.Password);
                cmd.Parameters.AddWithValue("@Name", user.Name);
                cmd.Parameters.AddWithValue("@Surname1", user.Surname1);
                cmd.Parameters.AddWithValue("@Surname2", user.Surname2);
                if (!string.IsNullOrEmpty(user.Address))
                    cmd.Parameters.AddWithValue("@Address", user.Address);
                else
                    cmd.Parameters.AddWithValue("@Address", DBNull.Value);
                if (!string.IsNullOrEmpty(user.CellPhone))
                    cmd.Parameters.AddWithValue("@CellPhone", user.CellPhone);
                else
                    cmd.Parameters.AddWithValue("@CellPhone", DBNull.Value);
                cmd.Parameters.AddWithValue("@Occupation", user.Occupation);
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                    return false;
                else
                    return true;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

        public static bool CheckIfExistsSameId(string id)
        {
            string sqlSentence = "SELECT ID FROM [Person] WHERE ID = @id";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@id",id);
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool CheckIfPasswordIsCorrect(string password, string id)
        {
            string sqlSentence = "SELECT Password FROM [Person] WHERE ID = @id";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@id",id);
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                    return false;

                reader.Read();
                string checkPassword = reader["Password"].ToString();
                if (checkPassword == password)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool UpdatePassword(string id, string password)
        {
            string sqlSentence = "UPDATE Person SET Password=@Password WHERE Id=@Id";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Password", password);
                cmd.Parameters.AddWithValue("@Id", id);
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                    return false;
                else
                    return true;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool UpdateUserData(User user)
        {
            string sqlSentence = "UPDATE Person SET Telephone=@Telephone, Name=@Name, Surname_1=@Surname1, Surname_2=@Surname2, Address=@Address, Cellular=@CellPhone, Ocupation=@Occupation WHERE Id=@Id";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (user == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Id", user.Id);
                if (!string.IsNullOrEmpty(user.Telephone))
                    cmd.Parameters.AddWithValue("@Telephone", user.Telephone);
                else
                    cmd.Parameters.AddWithValue("@Telephone", DBNull.Value);
                cmd.Parameters.AddWithValue("@Name", user.Name);
                cmd.Parameters.AddWithValue("@Surname1", user.Surname1);
                cmd.Parameters.AddWithValue("@Surname2", user.Surname2);
                if (!string.IsNullOrEmpty(user.Address))
                    cmd.Parameters.AddWithValue("@Address", user.Address);
                else
                    cmd.Parameters.AddWithValue("@Address", DBNull.Value);
                if (!string.IsNullOrEmpty(user.CellPhone))
                    cmd.Parameters.AddWithValue("@CellPhone", user.CellPhone);
                else
                    cmd.Parameters.AddWithValue("@CellPhone", DBNull.Value);

                cmd.Parameters.AddWithValue("@Occupation", user.Occupation);
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                    return false;
                else
                    return true;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

        public static DataTable SearchAUser(string id, string name, string surname1, string surname2)
        {
            string sqlSentence = "SELECT Id, Telephone, Password, Name, Surname_1, Surname_2, Address, Cellular, Ocupation FROM dbo.Person WHERE (Id LIKE @id) and (Name LIKE @name) and (Surname_1 LIKE @surname1) and (Surname_2 LIKE @surname2)";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@surname1", surname1);
                cmd.Parameters.AddWithValue("@surname2", surname2);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null) {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool DeleteUserData(string value)
        {
            string sqlSentence = "DELETE Person WHERE Id = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", value);
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
    }
}
